#include "pcrState.h"



bool PCRState::datao(QString outpath){

    // 创建xlsx文档
    QXlsx::Document xlsx;
    xlsx.addSheet("样本设置");
    xlsx.addSheet("原始数据");
    xlsx.addSheet("扩增数据");
    xlsx.addSheet("结果分析");

    xlsx.selectSheet("样本设置");       // =========================================== 样本设置
    xlsx.write(1,1,"仪器名称");         xlsx.write(1,2,"QPCRMulti");
    xlsx.write(2,1,"创建日期");         xlsx.write(2, 2, experimentRunTime);
    xlsx.write(3,1,"实验名称");         xlsx.write(3, 2, experimentName);
    xlsx.write(4,1,"实验结束时间");      xlsx.write(4, 2, experimentFinishTime);

    xlsx.write(6, 1, "孔位");
    xlsx.write(6, 2, "样本名称");
    xlsx.write(6, 3, "样本类型");
    xlsx.write(6, 4, "荧光染料");
    xlsx.write(6, 5, "浓度");
    int lineIdx = 7;

    foreach(CURVE curve, curveList.keys()){
        if(!curveList[curve]) { continue; }
        int posi = curve % 100;
        int channel = curve / 100;
        xlsx.write(lineIdx, 1, getPosiName(samplePannel[posi].posi));
        xlsx.write(lineIdx, 2, samplePannel[posi].name);
        xlsx.write(lineIdx, 3, samplePannel[posi].type);
        xlsx.write(lineIdx, 4, CHANNELLIST[channel]);
        xlsx.write(lineIdx, 5, samplePannel[posi].conc);
    }

    xlsx.selectSheet("原始数据");       // ============================================ 原始数据
    xlsx.write(1, 1, "孔位");
    xlsx.write(1, 2, "循环数");
    int colIdx = 3;
    foreach(CHANNEL channel, channelList.keys()){
        if(!channelList[channel])  continue;
        xlsx.write(1, colIdx, CHANNELLIST[channel]);
        colIdx ++;
    }

    lineIdx = 2;
    // 循环数
    for(int cycle = 1; cycle <= cycleNum; cycle ++){
        // 孔位
        foreach(POSI posi, posiList.keys()){

            if(!posiList[posi])  { continue; }

            xlsx.write(lineIdx, 1, posi);
            xlsx.write(lineIdx, 2, cycle);

            colIdx = 3;
            foreach(CHANNEL channel, channelList.keys()){

                CURVE curve = channel*100 + posi;

                if(!(rawData.contains(curve) && rawData[curve].size() > (cycle-1))){
                    continue;
                }
                double data = rawData[curve][cycle-1].y();
                xlsx.write(lineIdx, colIdx, data);
                colIdx ++;
            }
            lineIdx ++;
        }
    }

    xlsx.selectSheet("扩增数据");       // ============================================ 扩增数据
    xlsx.write(1, 1, "孔位");
    xlsx.write(1, 2, "循环数");
    xlsx.write(1, 3, "荧光染料");
    xlsx.write(1, 4, "Rn");
    xlsx.write(1, 5, "ΔRn");


    lineIdx = 2;

    foreach(CURVE curve, curveList.keys()){
        if(!curveList[curve])  { continue; }

        CHANNEL channel = curve / 100;
        POSI    posi = curve % 100;

        auto pointList = modelList[curve]->getFittedData();
        for(int i = 0; i < pointList.size(); i ++){

            if(pointList[i].x() == 0){
                continue;
            }
            xlsx.write(lineIdx, 1, getPosiName(posi));
            xlsx.write(lineIdx, 2, pointList[i].x());
            xlsx.write(lineIdx, 3, CHANNELLIST[channel]);
            if((!modelList.contains(curve))){
                lineIdx ++;
                continue;
            }
            xlsx.write(lineIdx, 4, pointList[i].y());
            xlsx.write(lineIdx, 5, modelList[curve]->getFittedData_delta()[i].y());

            lineIdx ++;
        }

    }


    xlsx.selectSheet("结果分析");       // ============================================ 结果分析

    xlsx.write(1,1,"仪器名称");         xlsx.write(1,2,"QPCRMulti");
    xlsx.write(2,1,"创建日期");         xlsx.write(2, 2, experimentRunTime);
    xlsx.write(3,1,"实验名称");         xlsx.write(3, 2, experimentName);
    xlsx.write(4,1,"实验结束时间");      xlsx.write(4, 2, experimentFinishTime);


    xlsx.write(6,1,"孔位");
    xlsx.write(6,2,"样本名称");
    xlsx.write(6,3,"样本类型");
    xlsx.write(6,4,"荧光染料");
    xlsx.write(6,5,"CT");
    xlsx.write(6,6,"浓度");
    xlsx.write(6,7,"浓度平均值");
    xlsx.write(6,8,"y轴截距");
    xlsx.write(6,9,"斜率");
    xlsx.write(6,10,"扩增效率");
    lineIdx = 7;
    foreach(CURVE curve, curveList.keys()){

        if(!curveList[curve])   { continue; }
        POSI posi = curve % 100;
        CHANNEL channel = curve / 100;

        xlsx.write(lineIdx, 1, getPosiName(posi));
        if(samplePannel.contains(posi))         xlsx.write(lineIdx, 2, samplePannel[posi].name);
        if(samplePannel.contains(posi))         xlsx.write(lineIdx, 3, samplePannel[posi].type);
        if(samplePannel.contains(posi))         xlsx.write(lineIdx, 4, channel);
        if(modelList.contains(curve))           xlsx.write(lineIdx, 5, modelList[curve]->getCt_relative());
        if(concList.contains(curve))            xlsx.write(lineIdx, 6, concList[curve]);
        if(avgConcList.contains(channel))       xlsx.write(lineIdx, 7, avgConcList[channel]);
        if(standardLine.contains(channel))      xlsx.write(lineIdx, 8, standardLine[channel][1]);
        if(modelList.contains(curve))           xlsx.write(lineIdx, 9, modelList[curve]->getSlope());
        if(modelList.contains(curve))           xlsx.write(lineIdx, 10, modelList[curve]->getEfficiency());
        lineIdx ++;

    }

    dataoGuide(xlsx);

    // 关闭文件
    if(xlsx.saveAs(outpath)){
        qDebug("导出成功");
        return true;
    }else{
        qDebug("导出失败");
        return false;
    }
}


bool PCRState::dataoGuide(QXlsx::Document& xlsx)
{

    if(!canGuide){
        xlsx.addSheet("下游操作建议");
        xlsx.selectSheet("下游操作建议");
        xlsx.write("A1","总数: "+QString::number(posiList.size()));
        xlsx.write("B1","筛选数量: 0");
        xlsx.write("C1","稀释数量: 0");
        xlsx.write("A2", "样品位置(所有样品)");
        xlsx.write("B2", "检材编号");
        xlsx.write("C2", "检测方案(链接常-STR模型“检测方案”)");
        xlsx.write("D2", "总稀释倍数DFa(链接常-STR模型相应“总稀释倍数DFa”)");
        xlsx.write("E2", "第一步稀释方案");
        xlsx.write("H2", "第二步稀释方案");
        xlsx.write("E2", "STR扩增模板体积(链接常、Mini-STR模型相应“STR扩增模板体积”)");
        xlsx.write("L2", "备注");
        xlsx.write("E3", "稀释倍数1 DFa1");
        xlsx.write("F3", "样品体积(ul)");
        xlsx.write("G3", "稀释液体积(ul)");
        xlsx.write("H3", "稀释倍数2 DFa1");
        xlsx.write("I3", "上一步稀释产物(ul)");
        xlsx.write("J3", "稀释液体积(ul)");

        int lineIdx = 4;
        foreach(POSI posi, posiList.keys()){
            QString sampleName = samplePannel[posi].name;
            xlsx.write("A"+QString::number(lineIdx), getPosiName(posi));
            xlsx.write("B"+QString::number(lineIdx), sampleName);
            xlsx.write("C"+QString::number(lineIdx), "无");
            lineIdx ++;
        }


        xlsx.addSheet("加样表格");
        xlsx.selectSheet("加样表格");
        xlsx.write("A1","常-STR加样表格");
        xlsx.write("A12","常-STR加样表格");
        xlsx.write("A23","常-STR加样表格");
        for(int i = 1; i <= 12; i ++){
            xlsx.write(QString('A'+i)+QString::number(2),  QString::number(i));
            xlsx.write(QString('A'+i)+QString::number(13), QString::number(i));
            xlsx.write(QString('A'+i)+QString::number(24), QString::number(i));
        }
        for(int i = 0; i < 8; i ++){
            xlsx.write("A"+QString::number(i+3),  QString('A'+i));
            xlsx.write("A"+QString::number(i+14), QString('A'+i));
            xlsx.write("A"+QString::number(i+25), QString('A'+i));
        }
        return false;
    }

    QList<QString> allInfo;
    QList<QString> YstrInfo;
    QList<QString> MiniStrInfo;

    if(false){
        return false;
    }
    else
    {

        int finalRowNum=4;
        xlsx.addSheet("下游操作建议");
        xlsx.selectSheet("下游操作建议");

        xlsx.write("A1","总数:");
        xlsx.write("B1","常STR:");
        xlsx.write("C1","mini-STR:");
        xlsx.write("D1","Y-STR数量:");
        xlsx.write("F1","需稀释样本数量:");

        xlsx.write("A2","样品位置(所有样品)");
        xlsx.write("B2","检材编号");
        xlsx.write("C2","检测方案(链接常-STR模型“检测方案”)");
        xlsx.write("D2","总稀释倍数DFa(链接常-STR模型相应“总稀释倍数DFa”)");
        xlsx.mergeCells("E2:G2");
        xlsx.write("E2","第一步稀释方案");
        xlsx.mergeCells("H2:J2");
        xlsx.write("H2","第二步稀释方案");
        xlsx.write("K2","STR扩增模板体积(链接常、Mini-STR模型相应“STR扩增模板体积”)");
        xlsx.write("L2","备注");

        xlsx.write("E3","稀释倍数1 \n DFa1");
        xlsx.write("F3","样品体积(ul)");
        xlsx.write("G3","稀释液体积(ul)");
        xlsx.write("H3","稀释倍数2 \n DFa1");
        xlsx.write("I3","上一步稀释产物(ul)");
        xlsx.write("J3","稀释液体积(ul)");

        QList<QString> label;
        label<<""<<"A"<<"B"<<"C"<<"D"<<"E"<<"F"<<"G"<<"H"<<"I"<<"J"<<"K"<<"L";

        tab2Fil("全部");

        if(table2Data.length()>0){
            for (int i=0;i<table2Data.count();i++)
            {
                QString pos=table2Data[i][0];
                if(pos==" "){
                    continue;
                }
                for (int m=1;m<label.length();m++) {
                    QString info;
                    QString item = table2Data[i][m-1];
                    if(item==" "){
                        info="——";
                    }else{
                        info =table2Data[i][m-1];

                    }
                    if(m==4 && (info=="——" || info=="无"))
                    {
                        xlsx.mergeCells("E"+QString::number(finalRowNum)+":"+"G"+QString::number(finalRowNum));
                        xlsx.write("E"+QString(finalRowNum),info);
                        m=7;
                    }
                    else if(m==7 && (info=="——" || info=="无")){
                        xlsx.mergeCells("H"+QString::number(finalRowNum)+":"+"J"+QString::number(finalRowNum));
                        xlsx.write("H"+QString::number(finalRowNum),info);
                        m=10;
                    }else{
                        xlsx.write(label[m]+QString::number(finalRowNum),info);
                    }
                }
                finalRowNum++;
            }
        }
        else{
            finalRowNum = 6;
        }

        xlsx.write("A"+QString::number(finalRowNum),"筛选:需稀释检材");

        xlsx.write("A"+QString::number(finalRowNum+1),"样品位置(所有样品)");
        xlsx.write("B"+QString::number(finalRowNum+1),"检材编号");
        xlsx.write("C"+QString::number(finalRowNum+1),"检测方案(链接常-STR模型“检测方案”)");
        xlsx.write("D"+QString::number(finalRowNum+1),"总稀释倍数DFa(链接常-STR模型相应“总稀释倍数DFa”)");
        xlsx.mergeCells("E"+QString::number(finalRowNum+1)+":"+"G"+QString::number(finalRowNum+1));
        xlsx.write("E"+QString::number(finalRowNum+1),"第一步稀释方案");
        xlsx.mergeCells("H"+QString::number(finalRowNum+1)+":"+"J"+QString::number(finalRowNum+1));
        xlsx.write("H"+QString::number(finalRowNum+1),"第二步稀释方案");
        xlsx.write("K"+QString::number(finalRowNum+1),"STR扩增模板体积(链接常、Mini-STR模型相应“STR扩增模板体积”)");
        xlsx.write("L"+QString::number(finalRowNum+1),"备注");

        xlsx.write("E"+QString::number(finalRowNum+2),"稀释倍数1 \r DFa1");
        xlsx.write("F"+QString::number(finalRowNum+2),"样品体积(ul)");
        xlsx.write("G"+QString::number(finalRowNum+2),"稀释液体积(ul)");
        xlsx.write("H"+QString::number(finalRowNum+2),"稀释倍数2 \n DFa1");
        xlsx.write("I"+QString::number(finalRowNum+2),"上一步稀释产物(ul)");
        xlsx.write("J"+QString::number(finalRowNum+2),"稀释液体积(ul)");

        tab2Fil("需稀释样本");
        if(table2Data.length()>0){
            finalRowNum=finalRowNum+3;
            for (int i=0;i<table2Data.count();i++)
            {
                QString pos=table2Data[i][0];
                if(pos==" "){
                    continue;
                }
                for (int m=1;m<label.length();m++) {
                    QString info;
                    QString item = table2Data[i][m-1];
                    if(item==" "){
                        info="——";
                    }else{
                        info =table2Data[i][m-1];

                    }
                    if(m==4 && (info=="——" || info=="无"))
                    {
                        xlsx.mergeCells("E"+QString::number(finalRowNum)+":"+"G"+QString::number(finalRowNum));
                        xlsx.write("E"+QString(finalRowNum),info);
                        m=7;
                    }
                    else if(m==7 && (info=="——" || info=="无")){
                        xlsx.mergeCells("H"+QString::number(finalRowNum)+":"+"J"+QString::number(finalRowNum));
                        xlsx.write("H"+QString::number(finalRowNum),info);
                        m=10;
                    }else{

                        xlsx.write(label[m]+QString::number(finalRowNum),info);
                    }
                }
                finalRowNum++;
            }
        }else{
            finalRowNum=11;
        }

        xlsx.write("A"+QString::number(finalRowNum),"筛选:需做Y-STR检材");

        xlsx.write("A"+QString::number(finalRowNum+1),"样品位置(所有样品)");
        xlsx.write("B"+QString::number(finalRowNum+1),"检材编号");
        xlsx.write("C"+QString::number(finalRowNum+1),"检测方案(链接常-STR模型“检测方案”)");
        xlsx.write("D"+QString::number(finalRowNum+1),"总稀释倍数DFa(链接常-STR模型相应“总稀释倍数DFa”)");
        xlsx.mergeCells("E"+QString::number(finalRowNum+1)+":"+"G"+QString::number(finalRowNum+1));
        xlsx.write("E"+QString::number(finalRowNum+1),"第一步稀释方案");
        xlsx.mergeCells("H"+QString::number(finalRowNum+1)+":"+"J"+QString::number(finalRowNum+1));
        xlsx.write("H"+QString::number(finalRowNum+1),"第二步稀释方案");
        xlsx.write("K"+QString::number(finalRowNum+1),"STR扩增模板体积(链接常、Mini-STR模型相应“STR扩增模板体积”)");
        xlsx.write("L"+QString::number(finalRowNum+1),"备注");

        xlsx.write("E"+QString::number(finalRowNum+2),"稀释倍数1 \r DFa1");
        xlsx.write("F"+QString::number(finalRowNum+2),"样品体积(ul)");
        xlsx.write("G"+QString::number(finalRowNum+2),"稀释液体积(ul)");
        xlsx.write("H"+QString::number(finalRowNum+2),"稀释倍数2 \n DFa1");
        xlsx.write("I"+QString::number(finalRowNum+2),"上一步稀释产物(ul)");
        xlsx.write("J"+QString::number(finalRowNum+2),"稀释液体积(ul)");


        tab2Fil("Y-STR");
        if(table2Data.length()>0){
            finalRowNum=finalRowNum+3;
            for (int i=0;i<table2Data.count();i++)
            {
                QString pos=table2Data[i][0];
                if(pos==" "){
                    continue;
                }
                for (int m=1;m<label.length();m++) {
                    QString info;
                    QString item = table2Data[i][m-1];
                    if(item==" "){
                        info="——";
                    }else{
                        info =table2Data[i][m-1];

                    }
                    if(m==4 && (info=="——" || info=="无"))
                    {
                        xlsx.mergeCells("E"+QString::number(finalRowNum)+":"+"G"+QString::number(finalRowNum));
                        xlsx.write("E"+QString(finalRowNum),info);
                        m=7;
                    }
                    else if(m==7 && (info=="——" || info=="无")){
                        xlsx.mergeCells("H"+QString::number(finalRowNum)+":"+"J"+QString::number(finalRowNum));
                        xlsx.write("H"+QString::number(finalRowNum),info);
                        m=10;
                    }else{

                        xlsx.write(label[m]+QString::number(finalRowNum),info);
                    }
                }
                finalRowNum++;
            }
        }
        else
        {
            finalRowNum=16;
        }

        xlsx.write("A"+QString::number(finalRowNum),"筛选:需做mini-STR检材");

        xlsx.write("A"+QString::number(finalRowNum+1),"样品位置(所有样品)");
        xlsx.write("B"+QString::number(finalRowNum+1),"检材编号");
        xlsx.write("C"+QString::number(finalRowNum+1),"检测方案(链接常-STR模型“检测方案”)");
        xlsx.write("D"+QString::number(finalRowNum+1),"总稀释倍数DFa(链接常-STR模型相应“总稀释倍数DFa”)");
        xlsx.mergeCells("E"+QString::number(finalRowNum+1)+":"+"G"+QString::number(finalRowNum+1));
        xlsx.write("E"+QString::number(finalRowNum+1),"第一步稀释方案");
        xlsx.mergeCells("H"+QString::number(finalRowNum+1)+":"+"J"+QString::number(finalRowNum+1));
        xlsx.write("H"+QString::number(finalRowNum+1),"第二步稀释方案");
        xlsx.write("K"+QString::number(finalRowNum+1),"STR扩增模板体积(链接常、Mini-STR模型相应“STR扩增模板体积”)");
        xlsx.write("L"+QString::number(finalRowNum+1),"备注");

        xlsx.write("E"+QString::number(finalRowNum+2),"稀释倍数1 \r DFa1");
        xlsx.write("F"+QString::number(finalRowNum+2),"样品体积(ul)");
        xlsx.write("G"+QString::number(finalRowNum+2),"稀释液体积(ul)");
        xlsx.write("H"+QString::number(finalRowNum+2),"稀释倍数2 \n DFa1");
        xlsx.write("I"+QString::number(finalRowNum+2),"上一步稀释产物(ul)");
        xlsx.write("J"+QString::number(finalRowNum+2),"稀释液体积(ul)");


        tab2Fil("mini-STR");
        if(table2Data.length()>0){
            finalRowNum=finalRowNum+3;
            for (int i=0;i<table2Data.count();i++)
            {
                QString pos=table2Data[i][0];
                if(pos==" "){
                    continue;
                }
                for (int m=1;m<label.length();m++) {
                    QString info;
                    QString item = table2Data[i][m-1];
                    if(item==" "){
                        info="——";
                    }else{
                        info =table2Data[i][m-1];

                    }
                    if(m==4 && (info=="——" || info=="无"))
                    {
                        xlsx.mergeCells("E"+QString::number(finalRowNum)+":"+"G"+QString::number(finalRowNum));
                        xlsx.write("E"+QString(finalRowNum),info);
                        m=7;
                    }
                    else if(m==7 && (info=="——" || info=="无")){
                        xlsx.mergeCells("H"+QString::number(finalRowNum)+":"+"J"+QString::number(finalRowNum));
                        xlsx.write("H"+QString::number(finalRowNum),info);
                        m=10;
                    }else{
                        xlsx.write(label[m]+QString::number(finalRowNum),info);
                    }
                }
                finalRowNum++;
            }
        }

        xlsx.addSheet("加样表格");
        xlsx.selectSheet("加样表格");

        QList<QString> rowList;
        rowList<<"B"<<"C"<<"D"<<"E"<<"F"<<"G"<<"H"<<"I"<<"J"<<"K"<<"L"<<"M";

        QList<QString> columList;
        columList<<"A"<<"B"<<"C"<<"D"<<"E"<<"F"<<"G"<<"H";


        xlsx.write("A1","常-STR加样表格");
        for (int i=0;i<rowList.length();i++) {
            xlsx.write(rowList[i]+"2",QString::number(i+1));
        }
        for (int i=0;i<columList.length();i++)
        {
            xlsx.write("A"+QString::number(i+3),columList[i]);
        }

        xlsx.write("A12","Y-STR加样表格");
        for (int i=0;i<rowList.length();i++) {
            xlsx.write(rowList[i]+"13",QString::number(i+1));
        }
        for (int i=0;i<columList.length();i++)
        {
            xlsx.write("A"+QString::number(i+14),columList[i]);
        }

        xlsx.write("A23","mini-STR加样表格");
        for (int i=0;i<rowList.length();i++) {
            xlsx.write(rowList[i]+"24",QString::number(i+1));
        }
        for (int i=0;i<columList.length();i++)
        {
            xlsx.write("A"+QString::number(i+25),columList[i]);
        }

        tab2Fil("全部");
        for (int i=0;i<table2Data.count();i++){
            QString pos=table2Data[i][0];
            if(pos==" "){
                continue;
            }

            QString info;
            QString item = table2Data[i][10];
            if(item==""){
                info="——";
            }else{
                info =table2Data[i][10];
            }

            QString rowInfo;
            QString colInfo;

            if(pos.length()==3){
                rowInfo= pos.left(1);
                colInfo=pos.right(2);
            }else{
                rowInfo= pos.left(1);
                colInfo=pos.right(1);
            }
            QString finalPos = rowList[colInfo.toInt()-1]+ QString::number(columList.indexOf(rowInfo)+3);
            xlsx.write(finalPos,info);
        }

        tab2Fil("Y-STR");
        for (int i=0;i<table2Data.count();i++){
            QString pos=table2Data[i][0];
            if(pos==" "){
                continue;
            }
            QString info;
            QString item = table2Data[i][10];
            if(item==""){
                info="——";
            }else{
                info =table2Data[i][10];
            }
            QString rowInfo;
            QString colInfo;

            if(pos.length()==3){
                rowInfo= pos.left(1);
                colInfo=pos.right(2);
            }else{
                rowInfo= pos.left(1);
                colInfo=pos.right(1);
            }


            QString finalPos = rowList[colInfo.toInt()-1]+ QString::number(columList.indexOf(rowInfo)+14);

            xlsx.write(finalPos,info);
        }

        tab2Fil("mini-STR");
        for (int i=0;i<table2Data.count();i++){
            QString pos=table2Data[i][0];
            if(pos==" "){
                continue;
            }
            QString info;
            QString item = table2Data[i][10];
            if(item==""){
                info="——";
            }else{
                info =table2Data[i][10];
            }
            QString rowInfo;
            QString colInfo;

            if(pos.length()==3){
                rowInfo= pos.left(1);
                colInfo=pos.right(2);
            }else{
                rowInfo= pos.left(1);
                colInfo=pos.right(1);
            }

            QString finalPos = rowList[colInfo.toInt()-1]+ QString::number(columList.indexOf(rowInfo)+25);

            xlsx.write(finalPos,info);
        }

        return true;
    }

}






